package main.dic;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;

import main.staff.CreateTypesSql;
import main.util.SqlConstants;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class CreateSql {

	public static FileWriter dict;
	static FileWriter del;
	
	public static void main(String[] args) {
		InputStream inp;
		try {
			inp = new FileInputStream("E:/互联网.xlsx");
			dict = new FileWriter("E:/工号.sql");
			 del=new FileWriter("E:/工号wrong.sql");
			 
			 
			Workbook wb = WorkbookFactory.create(inp);
			for(int i=0;i<33;i++){
				Sheet sheet=wb.getSheetAt(i);
				System.out.println(i+"============start"+sheet.getSheetName());
				operateSheet(sheet);
				System.out.println(i+"============end"+sheet.getSheetName());
			}
			 
			dict.close();
			del.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
		}
	}
	
	public static void operateSheet(Sheet sheet) throws IOException{
		int col=4;
		/*for(int i=0;)*/
		
		if(sheet==null){
			return;
		}
		
		for(int i=0;i<sheet.getLastRowNum()+1;i++){
			Row row=sheet.getRow(i);
			String staffid=SqlConstants.getColValue(row, col).trim();
			System.out.println(staffid);
			
			if("业务工号".equals(staffid) || "".equals(staffid)){
				continue;
			}
			
			if(staffid.matches("[a-zA-Z]{2}[0-9]{5}") || staffid.matches("[a-zA-Z]{3}?[0-9]{4}?")){
				dict.write("'"+staffid+"',");
			}else{
				del.write("'"+staffid+"',");
			}
		}
	}
	
}
